SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConsumableType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConsumableType](
	[ConsumableTypeID] [int] NOT NULL,
	[ConsumableType] [char](10) NOT NULL,
 CONSTRAINT [ConsumableType_PK] PRIMARY KEY CLUSTERED 
(
	[ConsumableTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Employee](
	[EmployeeID] [char](10) NOT NULL,
	[EmployeeName] [varchar](50) NOT NULL,
	[ContactNo] [char](20) NOT NULL,
	[Email] [varchar](30) NOT NULL,
 CONSTRAINT [Employee_PK] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeviceStatus]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DeviceStatus](
	[DeviceStatusID] [int] NOT NULL,
	[DeviceStatus] [char](10) NOT NULL,
 CONSTRAINT [DeviceStatus_PK] PRIMARY KEY CLUSTERED 
(
	[DeviceStatusID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Status]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Status](
	[StatusID] [int] NOT NULL,
	[Status] [char](10) NOT NULL,
 CONSTRAINT [Status_PK] PRIMARY KEY CLUSTERED 
(
	[StatusID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OfficeItems]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OfficeItems](
	[ItemID] [char](10) NOT NULL,
	[CategoryID] [char](6) NOT NULL,
	[ItemName] [varchar](50) NOT NULL,
	[Brand] [varchar](50) NULL,
	[Model] [varchar](50) NULL,
	[ItemDesc] [varchar](500) NULL,
	[Quantity] [int] NOT NULL,
	[Threshold] [int] NOT NULL,
	[Price] [decimal](10, 2) NOT NULL,
	[ConsumableTypeID] [int] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[UpdateDate] [datetime] NULL,
 CONSTRAINT [OfficeItems_PK] PRIMARY KEY CLUSTERED 
(
	[ItemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ItemDetails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ItemDetails](
	[ItemDetailID] [char](12) NOT NULL,
	[ItemID] [char](10) NOT NULL,
	[SerialNo] [char](10) NOT NULL,
	[DeviceStatusID] [int] NOT NULL,
	[StatusID] [int] NOT NULL,
	[Location] [varchar](50) NULL,
	[CreateDate] [datetime] NOT NULL,
	[UpdateDate] [datetime] NULL,
 CONSTRAINT [ItemDetails_PK] PRIMARY KEY CLUSTERED 
(
	[ItemDetailID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReplenishTransaction]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ReplenishTransaction](
	[ReplenishTransID] [char](11) NOT NULL,
	[ItemDetailID] [char](12) NOT NULL,
	[Quantity] [int] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
 CONSTRAINT [ReplenishTransaction_PK] PRIMARY KEY CLUSTERED 
(
	[ReplenishTransID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IssueTransaction]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[IssueTransaction](
	[IssueTransID] [char](11) NOT NULL,
	[ItemDetailID] [char](12) NOT NULL,
	[EmployeeID] [char](10) NOT NULL,
	[Quantity] [int] NOT NULL,
	[UsedStartTime] [datetime] NOT NULL,
	[UsedEndTime] [datetime] NULL,
 CONSTRAINT [IssueTransaction_PK] PRIMARY KEY CLUSTERED 
(
	[IssueTransID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Category]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Category](
	[CategoryID] [char](6) NOT NULL,
	[CategoryName] [varchar](50) NOT NULL,
	[ConsumableTypeID] [int] NOT NULL,
 CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[User](
	[EmployeeID] [char](10) NOT NULL,
	[Password] [binary](20) NOT NULL,
 CONSTRAINT [User_PK] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReturnTransaction]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ReturnTransaction](
	[ReturnTransID] [char](12) NOT NULL,
	[IssueTransID] [char](11) NOT NULL,
	[DeviceStatusID] [int] NOT NULL,
	[Remarks] [char](500) NULL,
	[CreateDate] [datetime] NOT NULL,
 CONSTRAINT [ReturnTransaction_PK] PRIMARY KEY CLUSTERED 
(
	[ReturnTransID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Category_OfficeItems_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeItems]'))
ALTER TABLE [dbo].[OfficeItems]  WITH CHECK ADD  CONSTRAINT [Category_OfficeItems_FK1] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[OfficeItems] CHECK CONSTRAINT [Category_OfficeItems_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ConsumableType_OfficeItems_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeItems]'))
ALTER TABLE [dbo].[OfficeItems]  WITH CHECK ADD  CONSTRAINT [ConsumableType_OfficeItems_FK1] FOREIGN KEY([ConsumableTypeID])
REFERENCES [dbo].[ConsumableType] ([ConsumableTypeID])
GO
ALTER TABLE [dbo].[OfficeItems] CHECK CONSTRAINT [ConsumableType_OfficeItems_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[DeviceStatus_ItemDetails_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ItemDetails]'))
ALTER TABLE [dbo].[ItemDetails]  WITH CHECK ADD  CONSTRAINT [DeviceStatus_ItemDetails_FK1] FOREIGN KEY([DeviceStatusID])
REFERENCES [dbo].[DeviceStatus] ([DeviceStatusID])
GO
ALTER TABLE [dbo].[ItemDetails] CHECK CONSTRAINT [DeviceStatus_ItemDetails_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[OfficeItems_ItemDetails_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ItemDetails]'))
ALTER TABLE [dbo].[ItemDetails]  WITH CHECK ADD  CONSTRAINT [OfficeItems_ItemDetails_FK1] FOREIGN KEY([ItemID])
REFERENCES [dbo].[OfficeItems] ([ItemID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ItemDetails] CHECK CONSTRAINT [OfficeItems_ItemDetails_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Status_ItemDetails_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ItemDetails]'))
ALTER TABLE [dbo].[ItemDetails]  WITH CHECK ADD  CONSTRAINT [Status_ItemDetails_FK1] FOREIGN KEY([StatusID])
REFERENCES [dbo].[Status] ([StatusID])
GO
ALTER TABLE [dbo].[ItemDetails] CHECK CONSTRAINT [Status_ItemDetails_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ItemDetails_ReplenishTransaction_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReplenishTransaction]'))
ALTER TABLE [dbo].[ReplenishTransaction]  WITH CHECK ADD  CONSTRAINT [ItemDetails_ReplenishTransaction_FK1] FOREIGN KEY([ItemDetailID])
REFERENCES [dbo].[ItemDetails] ([ItemDetailID])
GO
ALTER TABLE [dbo].[ReplenishTransaction] CHECK CONSTRAINT [ItemDetails_ReplenishTransaction_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Employee_IssueTransaction_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[IssueTransaction]'))
ALTER TABLE [dbo].[IssueTransaction]  WITH CHECK ADD  CONSTRAINT [Employee_IssueTransaction_FK1] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO
ALTER TABLE [dbo].[IssueTransaction] CHECK CONSTRAINT [Employee_IssueTransaction_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ItemDetails_IssueTransaction_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[IssueTransaction]'))
ALTER TABLE [dbo].[IssueTransaction]  WITH CHECK ADD  CONSTRAINT [ItemDetails_IssueTransaction_FK1] FOREIGN KEY([ItemDetailID])
REFERENCES [dbo].[ItemDetails] ([ItemDetailID])
GO
ALTER TABLE [dbo].[IssueTransaction] CHECK CONSTRAINT [ItemDetails_IssueTransaction_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ConsumableType_Category_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[Category]'))
ALTER TABLE [dbo].[Category]  WITH CHECK ADD  CONSTRAINT [ConsumableType_Category_FK1] FOREIGN KEY([ConsumableTypeID])
REFERENCES [dbo].[ConsumableType] ([ConsumableTypeID])
GO
ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [ConsumableType_Category_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Employee_User_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[User]'))
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [Employee_User_FK1] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [Employee_User_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[DeviceStatus_ReturnTransaction_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReturnTransaction]'))
ALTER TABLE [dbo].[ReturnTransaction]  WITH CHECK ADD  CONSTRAINT [DeviceStatus_ReturnTransaction_FK1] FOREIGN KEY([DeviceStatusID])
REFERENCES [dbo].[DeviceStatus] ([DeviceStatusID])
GO
ALTER TABLE [dbo].[ReturnTransaction] CHECK CONSTRAINT [DeviceStatus_ReturnTransaction_FK1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[IssueTransaction_ReturnTransaction_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReturnTransaction]'))
ALTER TABLE [dbo].[ReturnTransaction]  WITH CHECK ADD  CONSTRAINT [IssueTransaction_ReturnTransaction_FK1] FOREIGN KEY([IssueTransID])
REFERENCES [dbo].[IssueTransaction] ([IssueTransID])
GO
ALTER TABLE [dbo].[ReturnTransaction] CHECK CONSTRAINT [IssueTransaction_ReturnTransaction_FK1]
GO
